﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;
using System.Windows.Forms;

namespace MarginTrading.Services
{
    public static class ClientService
    {
        private static Client GetClientFromDataReader(IDataReader dataReader)
        {
            Client retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Client();
                retVal.Code = dataReader["CODE"].ToString();
                retVal.Title = dataReader["TITLE"].ToString();
                retVal.Initials = dataReader["INITIALS"].ToString();
                retVal.OtherNames = dataReader["OTHER_NAMES"].ToString();
                retVal.Surname = dataReader["SURNAME"].ToString();
                retVal.Address_1 = dataReader["ADDRESS_1"].ToString();
                retVal.Address_2 = dataReader["ADDRESS_2"].ToString();
                retVal.Address_3 = dataReader["ADDRESS_3"].ToString();
                retVal.Address_4 = dataReader["ADDRESS_4"].ToString();
                retVal.Advisor = dataReader["ADVISOR"].ToString();
                DateTime regDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["REG_DATE"].ToString(),out regDate))
                    retVal.RegDate = regDate;
                DateTime MTRegDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["MARGIN_REG_DATE"].ToString(), out MTRegDate))
                    retVal.MTRegDate = MTRegDate;
                retVal.Agent = dataReader["AGENT"].ToString();
                retVal.Telephone1 = dataReader["TELEPHONE_1"].ToString();
                retVal.Telephone2 = dataReader["TELEPHONE_2"].ToString();
                retVal.Fax = dataReader["FAX"].ToString();
				retVal.Broker = dataReader["BROKER"].ToString();
                double marginLimit = 0;
                if (double.TryParse(dataReader["MARGIN_LIMIT"].ToString(), out marginLimit))
                {
                    retVal.MarginLimit = marginLimit;
                }

                int StaffMmember = 0;
                if (Int32.TryParse(dataReader["MARGIN_CLIENT"].ToString(), out StaffMmember))
                {
                    retVal.MarginClient = StaffMmember;                   
                }
                else
                {
                    retVal.MarginClient = 0;
                }

                double interestRate = 0;
                if (double.TryParse(dataReader["INTEREST_RATE"].ToString(), out interestRate))
                {
                    retVal.InterestRate = interestRate;
                }
            }
            return retVal;
        }

        public static Client GetClientByCode(string code)
        {
            Client retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client where code = '" + code + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetClientFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static ClientCollection GetClientCollection()
        {
            ClientCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new ClientCollection();
                    while (dataReader.Read())
                    {
                        Client item = GetClientFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                 throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }



        public static ClientCollection GetClientCollectionEX(String Agent)
        {
            ClientCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client where margin_client = 1 and agent = '"+ Agent + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new ClientCollection();
                    while (dataReader.Read())
                    {
                        Client item = GetClientFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }



        //public static List<string> GetClientCodeCollection(string agent)
        //{
        //    List<string> retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select CODE from client where agent = '" + agent + "'";
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            retVal = new List<string>();
        //            while (dataReader.Read())
        //            {
        //                string item = dataReader["CODE"].ToString();
        //                if (!string.IsNullOrEmpty(item))
        //                    retVal.Add(item);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}

        public static List<string> GetClientCodeCollection()
        {
            List<string> retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CODE from client";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new List<string>();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CODE"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetClientCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                    throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static AutoCompleteStringCollection GetClientCodeACCollection()
        {
            AutoCompleteStringCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CODE FROM CLIENT";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AutoCompleteStringCollection();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CODE"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static AutoCompleteStringCollection GetClientNameCollection()
        {
            AutoCompleteStringCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CLIENT.SURNAME || ' ' || CLIENT.INITIALS || ' (' || CLIENT.CODE || ')'" +
                                  "as  CLIENTNAME FROM CLIENT";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AutoCompleteStringCollection();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CLIENTNAME"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static List<string> GetClientCodeCollection(string agent)
        {
            List<string> retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CODE from client where agent = '" + agent + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new List<string>();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CODE"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Client client)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into client(CODE,TITLE,INITIALS,OTHER_NAMES,SURNAME,ADDRESS_1,ADDRESS_2,ADDRESS_3," +
                                  "ADDRESS_4,ADVISOR,REG_DATE,AGENT,TELEPHONE_1,TELEPHONE_2,FAX,MARGIN_LIMIT,MARGIN_CLIENT,BROKER, INTEREST_RATE";

                if (client.MTRegDate != DateTime.MinValue)
                {
                    cmd.CommandText += ", MARGIN_REG_DATE) ";
                    cmd.Parameters.Add(":v19", OracleType.DateTime).Value = client.MTRegDate;
                }
                else
                {
                    cmd.CommandText += ")";
                }
                cmd.CommandText += "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10,:v11,:v12,:v13,:v14,:v15,:v16,:v17,:v18, :v20";
                if (client.MTRegDate != DateTime.MinValue)
                {
                    cmd.CommandText += ", :v19)";
                    cmd.Parameters.Add(":v19", OracleType.DateTime).Value = client.MTRegDate;
                }
                else
                {
                    cmd.CommandText += ")";
                }
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = client.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = client.Title;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = client.Initials;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = client.OtherNames;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = client.Surname;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = client.Address_1;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = client.Address_2;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = client.Address_3;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = client.Address_4;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = client.Advisor;
                cmd.Parameters.Add(":v11", OracleType.DateTime).Value = client.RegDate;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = client.Agent;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = client.Telephone1;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = client.Telephone2;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = client.Fax;
                cmd.Parameters.Add(":v16", OracleType.Number).Value = client.MarginLimit;
                cmd.Parameters.Add(":v17", OracleType.Number).Value = client.MarginClient;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = client.Broker;
                cmd.Parameters.Add(":v20", OracleType.Number).Value = client.InterestRate;
               
                cmd.Connection = con;

                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string code)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from client where code = '" + code + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Client client)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update client set " +
                                  "TITLE = :v2, " +
                                  "INITIALS = :v3, " +
                                  "OTHER_NAMES = :v4, " +
                                  "SURNAME = :v5, " +
                                  "ADDRESS_1 = :v6, " +
                                  "ADDRESS_2 = :v7, " +
                                  "ADDRESS_3 = :v8, " +
                                  "ADDRESS_4 = :v9, " +
                                  "ADVISOR = :v10, " +
                                  "REG_DATE = :v11, " +
                                  "AGENT = :v12, " +
                                  "TELEPHONE_1 = :v13, " +
                                  "TELEPHONE_2 = :v14, " +
                                  "FAX = :v15, " +
                                  "MARGIN_LIMIT = :v16, " +
                                  "MARGIN_CLIENT = :v17, " +
                                  "INTEREST_RATE = :v19";
                
                if (client.MTRegDate != DateTime.MinValue)
                {
                    cmd.CommandText += ", MARGIN_REG_DATE = :v18";
                    cmd.Parameters.Add(":v18", OracleType.DateTime).Value = client.MTRegDate;
                }
                else
                {
                    cmd.CommandText += ", MARGIN_REG_DATE = :v18";
                    cmd.Parameters.Add(":v18", OracleType.DateTime).Value = DateTime.MinValue;
                }
                cmd.CommandText += " where CODE = :v1";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = client.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = client.Title;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = client.Initials;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = client.OtherNames;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = client.Surname;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = client.Address_1;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = client.Address_2;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = client.Address_3;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = client.Address_4;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = client.Advisor;
                cmd.Parameters.Add(":v11", OracleType.DateTime).Value = client.RegDate;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = client.Agent;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = client.Telephone1;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = client.Telephone2;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = client.Fax;
                cmd.Parameters.Add(":v16", OracleType.Number).Value = client.MarginLimit;
                cmd.Parameters.Add(":v17", OracleType.Number).Value = client.MarginClient;
                cmd.Parameters.Add(":v19", OracleType.Number).Value = client.InterestRate;
               
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }


        public static Dictionary<string, double> GetClientCodeInterestCollection(String AgentCode) // Ravin
        {
            Dictionary<string, double> retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CODE, INTEREST_RATE from client where AGENT = '"+AgentCode+"'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new Dictionary<string, double>();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CODE"].ToString();
                        double rate = 0.0f;
                        double.TryParse(dataReader["INTEREST_RATE"].ToString(), out rate);
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item, rate);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

		public static ClientCollection GetNewMTClientCollection(DateTime MTRegDate, String Agent)
        {
            ClientCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client where margin_client = 1 and agent = '" + Agent + "' " +
                                  "and TO_CHAR(reg_date,'mm/dd/yyyy') = '" + MTRegDate.ToString("MM/dd/yyyy") + "'";

                //cmd.Parameters.Add(":v1", OracleType.DateTime).Value = MTRegDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new ClientCollection();
                    while (dataReader.Read())
                    {
                        Client item = GetClientFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static List<string> GetClientCodeCol(string agent)
        {
            List<string> retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CODE from client where margin_client = 1 and agent = '" + agent + "'";
                //cmd.CommandText = "select CODE from client where margin_client = 1";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new List<string>();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CODE"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }



    }
}
